package com.iflytek.voicecloud.rtasr.service;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.iflytek.voicecloud.rtasr.config.AppConfig;
import com.iflytek.voicecloud.rtasr.model.Answers;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.time.Instant;
import java.util.List;
import java.util.UUID;

@Component
public class DBCreate {


    private final RedisTemplate<String, Object> redisTemplate;
    private final Connection dbConnection;

    public DBCreate(AppConfig appConfig, RedisTemplate<String, Object> redisTemplate) throws SQLException {
        this.redisTemplate = redisTemplate;
        this.dbConnection = initializeDatabaseConnection(appConfig);
    }

    private Connection initializeDatabaseConnection(AppConfig appConfig) throws SQLException {
        try {
            Class.forName(appConfig.getDbDriverClass());
            return DriverManager.getConnection(
                    appConfig.getDbUrl(),
                    appConfig.getDbUsername(),
                    appConfig.getDbPassword()
            );
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException("Failed to initialize database connection", e);
        }
    }

    public void receiveResults(
            String lastEnhancedResult,
            UUID interviewId,
            LocalDateTime startTime,
            LocalDateTime endTime,
            int questionOrder
    ) throws Exception {
        try {
            System.out.println("处理的回答"+lastEnhancedResult);
            // 构造 Redis 键
            String questionKey = "interview:" + interviewId + ":question:" + questionOrder;
            // 构造 Redis 键（沿用用户选中的构造方式）
            String answerKey = "interview:" + interviewId + ":answer:" + questionOrder;
            // 存储 lastEnhancedResult 到 Redis
            redisTemplate.opsForValue().set(answerKey, lastEnhancedResult);


            // 从 Redis 获取 JSON 字符串
            String questionJson = (String) redisTemplate.opsForValue().get(questionKey);
            if (questionJson == null) {
                throw new RuntimeException("Question data not found for order: " + questionOrder);
            }

            // 解析 JSON 并提取 question_id
            UUID questionId = UUID.fromString(
                    new ObjectMapper().readTree(questionJson).get("question_id").asText()
            );

            // 构建 Answers 对象
            Answers answer = new Answers();
            answer.setAnswerId(UUID.randomUUID());
            answer.setQuestionId(questionId); // 设置从 Redis 提取的 question_id
            answer.setInterviewId(interviewId);
            answer.setTranscriptText(lastEnhancedResult);
            answer.setStartTime(startTime);
            answer.setEndTime(endTime);

            insertAnswer(answer);

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("数据库插入失败: " + e.getMessage(), e);
        }
    }


    private void insertAnswer(Answers answer) throws Exception {
        String sql = "INSERT INTO answers (answer_id, question_id, interview_id, transcript_text, start_time, end_time) VALUES (?, ?, ?, ?, ?, ?)";
        try (PreparedStatement stmt = dbConnection.prepareStatement(sql)) {
            // ✅ 显式调用 toString() 并使用 setString
            stmt.setString(1, answer.getAnswerId().toString());
            stmt.setString(2, answer.getQuestionId().toString());
            stmt.setString(3, answer.getInterviewId().toString());
            stmt.setString(4, answer.getTranscriptText());
            stmt.setObject(5, answer.getStartTime());
            stmt.setObject(6, answer.getEndTime());

            // ✅ 插入成功后输出信息
            int rowsAffected = stmt.executeUpdate();
            if (rowsAffected > 0) {
                System.out.println("✅ 数据库插入成功 | answer_id=" + answer.getAnswerId() +
                        ", interview_id=" + answer.getInterviewId() +
                        ", question_id=" + answer.getQuestionId());
            }
        } catch (SQLException e) {
            throw new Exception("数据库插入失败: " + e.getMessage(), e);
        }
    }
}



